SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[incumbencias2] 

@cargo as int,@asignatura as int  AS

/*esta rutina es para incluir en todos los grupos de incumbencias a los cargos mencionados mas abajo)
serìa equivalente a decir por ejemplo que el cargo 55 tiene incumbencia 99  */

SET NOCOUNT ON
declare @cargogenerico as int,@asignaturagenerica as int, @grupo as int
declare @i as integer,@j as integer

set @i=0
set @j=0

declare CargoAsignatura cursor SCROLL for 
	select cargo_definitivo,asignatura_definitiva from mig.dbo.cargos_asignaturas_ceprode2 
	where cargo_definitivo <>0 and cargo_definitivo in(160,161,162,163,164,165,167,169) and asignatura_definitiva in(0) order by 1,2

open CargoAsignatura

fetch first  from CargoAsignatura  into @CargoGenerico,@AsignaturaGenerica 

declare GrupoInc cursor SCROLL for
--	select distinct idgrupoincumbencia from tb_gruposDeIncumbencias where idgrupoincumbencia not in( 10,99,564,565,1,20,22,23)  order by idgrupoincumbencia
	select distinct idgrupoincumbencia 
	from tb_gruposDeIncumbencias join
	tb_incumbencias on idgrupoincumbencia = grupoincumbencia 
	where idgrupoincumbencia  in( 10) or PATINDEX('%- 10 -%', Descripcion) > 0  
	order by idgrupoincumbencia

open GrupoInc


while @@FETCH_STATUS = 0
begin
	fetch first from GrupoInc  into @grupo
--	fetch next from GrupoInc into @grupo
	while @@FETCH_STATUS = 0
	begin
		print 'insert tb_incumbencias (grupo,cargo,asignatura)  : values( ' + cast(@grupo as varchar) +'  ' + cast(@CargoGenerico as varchar) +'  ' + cast(@AsignaturaGenerica as varchar) + ')'
		insert tb_incumbencias (grupoincumbencia,cargoGenerico,asignaturaGenerica)  values(@grupo,@cargoGenerico,@AsignaturaGenerica)
		fetch next from GrupoInc into @grupo
		set @i=@i+1
	end
	fetch next from CargoAsignatura into @CargoGenerico,@AsignaturaGenerica 
	set @j=@j+1
end

close Cargoasignatura
deallocate Cargoasignatura

close GrupoInc
deallocate GrupoInc

print ' Cantidad de incumbencias afectadas ' + cast(@i as varchar)
print ' Cantidad de Cargos ,Asignaturas  ' + cast(@j as varchar)
GO
